package com.pkdl.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.pkdl.dto.NguoiDungDTO;
import com.pkdl.enums.LoaiNguoiDung;

// Data access object, which get data of NguoiDung table from database
public class NguoiDungDAO {
	public boolean delete(String manguoidung) {
		boolean result = true;
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_NguoiDung_Delete(?)}");
			cs.setString(1, manguoidung);
			int n = cs.executeUpdate();
			if (n == 0) {
				result = false;
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(NguoiDungDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(NguoiDungDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return result;
	}

	public boolean insert(NguoiDungDTO nguoidung) {
		boolean result = true;
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_NguoiDung_Insert(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
			cs.setString(1, nguoidung.getMaNguoiDung());
			cs.setString(2, nguoidung.getLoaiNguoiDung());
			cs.setString(3, nguoidung.getHoTen());
			cs.setBoolean(4, nguoidung.getGioiTinh());
			cs.setDate(5, new java.sql.Date(nguoidung.getNgaySinh().getTime()));
			cs.setString(6, nguoidung.getNoiSinh());
			cs.setDate(7,
					new java.sql.Date(nguoidung.getNgayVaoLam().getTime()));
			cs.setString(8, nguoidung.getNoiOHienTai());
			cs.setString(9, nguoidung.getDTDD());
			cs.setString(10, nguoidung.getDTNha());
			cs.setString(11, nguoidung.getEmail());
			cs.setString(12, nguoidung.getChuyenMon());
			cs.setString(13, nguoidung.getMatKhau());
			cs.setLong(14, nguoidung.getLuong());
			int n = cs.executeUpdate();
			if (n == 0) {
				result = false;
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(NguoiDungDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(NguoiDungDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return result;
	}

	public ArrayList<NguoiDungDTO> selectAll() {
		ArrayList<NguoiDungDTO> list = new ArrayList<NguoiDungDTO>();
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_NguoiDung_SelectAll()}");
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				if (!rs.getString("LoaiNguoiDung").equals(
						LoaiNguoiDung.QUANTRIVIEN.toString())) {
					NguoiDungDTO nguoidung = new NguoiDungDTO();
					nguoidung.setMaNguoiDung(rs.getString("MaNguoiDung"));
					nguoidung.setLoaiNguoiDung(rs.getString("LoaiNguoiDung"));
					nguoidung.setHoTen(rs.getString("HoTen"));
					nguoidung.setGioiTinh(rs.getBoolean("GioiTinh"));
					nguoidung.setNgaySinh(rs.getDate("NgaySinh"));
					nguoidung.setNoiSinh(rs.getString("NoiSinh"));
					nguoidung.setNgayVaoLam(rs.getDate("NgayVaoLam"));
					nguoidung.setNoiOHienTai(rs.getString("NoiOHienTai"));
					nguoidung.setDTDD(rs.getString("DTDD"));
					nguoidung.setDTNha(rs.getString("DTNha"));
					nguoidung.setEmail(rs.getString("Email"));
					nguoidung.setChuyenMon(rs.getString("ChuyenMon"));
					nguoidung.setMatKhau(rs.getString("MatKhau"));
					nguoidung.setLuong(rs.getLong("Luong"));
					list.add(nguoidung);
				}
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(NguoiDungDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(NguoiDungDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return list;
	}

	public NguoiDungDTO selectById(String manguoidung) {
		NguoiDungDTO nguoidung = null;
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_NguoiDung_SelectByID(?)}");
			cs.setString(1, manguoidung);
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				nguoidung = new NguoiDungDTO();
				nguoidung.setMaNguoiDung(rs.getString("MaNguoiDung"));
				nguoidung.setLoaiNguoiDung(rs.getString("LoaiNguoiDung"));
				nguoidung.setHoTen(rs.getString("HoTen"));
				nguoidung.setGioiTinh(rs.getBoolean("GioiTinh"));
				nguoidung.setNgaySinh(rs.getDate("NgaySinh"));
				nguoidung.setNoiSinh(rs.getString("NoiSinh"));
				nguoidung.setNgayVaoLam(rs.getDate("NgayVaoLam"));
				nguoidung.setNoiOHienTai(rs.getString("NoiOHienTai"));
				nguoidung.setDTDD(rs.getString("DTDD"));
				nguoidung.setDTNha(rs.getString("DTNha"));
				nguoidung.setEmail(rs.getString("Email"));
				nguoidung.setChuyenMon(rs.getString("ChuyenMon"));
				nguoidung.setMatKhau(rs.getString("MatKhau"));
				nguoidung.setLuong(rs.getLong("Luong"));
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(NguoiDungDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(NguoiDungDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return nguoidung;
	}

	public NguoiDungDTO selectByIdAndPassword(String manguoidung, String matkhau) {
		NguoiDungDTO nguoidung = null;
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_NguoiDung_SelectByIDAndPassword(?, ?)}");
			cs.setString(1, manguoidung);
			cs.setString(2, matkhau);
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				nguoidung = new NguoiDungDTO();
				nguoidung.setMaNguoiDung(rs.getString("MaNguoiDung"));
				nguoidung.setLoaiNguoiDung(rs.getString("LoaiNguoiDung"));
				nguoidung.setHoTen(rs.getString("HoTen"));
				nguoidung.setGioiTinh(rs.getBoolean("GioiTinh"));
				nguoidung.setNgaySinh(rs.getDate("NgaySinh"));
				nguoidung.setNoiSinh(rs.getString("NoiSinh"));
				nguoidung.setNgayVaoLam(rs.getDate("NgayVaoLam"));
				nguoidung.setNoiOHienTai(rs.getString("NoiOHienTai"));
				nguoidung.setDTDD(rs.getString("DTDD"));
				nguoidung.setDTNha(rs.getString("DTNha"));
				nguoidung.setEmail(rs.getString("Email"));
				nguoidung.setChuyenMon(rs.getString("ChuyenMon"));
				nguoidung.setMatKhau(rs.getString("MatKhau"));
				nguoidung.setLuong(rs.getLong("Luong"));
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(NguoiDungDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(NguoiDungDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return nguoidung;
	}

	public boolean update(NguoiDungDTO nguoidung) {
		boolean result = true;
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_NguoiDung_Update(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
			cs.setString(1, nguoidung.getMaNguoiDung());
			cs.setString(2, nguoidung.getLoaiNguoiDung());
			cs.setString(3, nguoidung.getHoTen());
			cs.setBoolean(4, nguoidung.getGioiTinh());
			cs.setDate(5, new java.sql.Date(nguoidung.getNgaySinh().getTime()));
			cs.setString(6, nguoidung.getNoiSinh());
			cs.setDate(7,
					new java.sql.Date(nguoidung.getNgayVaoLam().getTime()));
			cs.setString(8, nguoidung.getNoiOHienTai());
			cs.setString(9, nguoidung.getDTDD());
			cs.setString(10, nguoidung.getDTNha());
			cs.setString(11, nguoidung.getEmail());
			cs.setString(12, nguoidung.getChuyenMon());
			cs.setString(13, nguoidung.getMatKhau());
			cs.setLong(14, nguoidung.getLuong());
			int n = cs.executeUpdate();
			if (n == 0) {
				result = false;
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(NguoiDungDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(NguoiDungDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return result;
	}

}
